<?php
//session_start();
/*
Plugin Name: Synergy Asset Management
Plugin URI: http://www.synergy.com/
Description: Asset management wordpress plugin for Synergy Resource management system.
Version: 1.0
Author: Weranga Kaluarachchi
Author URI: http://www.synergy.com
License: GPL
*/

$selProjID;
$selProjName= "";
wp_enqueue_script('syn_asset_mgmt_script', '/wp-content/plugins/syn_asset_mgmt/syn_asset_mgmt.js');


/**
 * Main method of the syn_asset_mgmt plugin
 */
function syn_load_assets() {
?>

<?php 

	global $is_iphone;
	$selProjID = syn_get_current_project();
	//$selProjID = -1;
	echo "<div class='mainPanel'>\n";
	if($selProjID == -1) {
		echo "<h4>No Project Selected</h4><br/>";
		echo "<b>Please select a project from the left side by clicking on its title or do a search on the right side.</b><br/>";
	}
	else {
		if(isset($_POST[btnSynSearchSubmit]) && $_POST[btnSynSearchSubmit] == "find") {
			$searchProjName = "";
			if(isset($_POST[ldbSynSearchProject])) {
				foreach($_POST[ldbSynSearchProject] as $searchProjID) {
					$searchProjName .= " ".get_project_by_ID($searchProjID);
				}
				echo "<h4 id='lblProjectHeading'>".$searchProjName."</h4>";
			}
		}
		else {
			$selProjName = get_project_by_ID($selProjID);
			echo "<h4 id='lblProjectHeading'>".$selProjName."</h4>";
		}
		
	}
	echo "<form class='assetForm' action='$PHP_SELF' method='post' enctype='multipart/form-data'>";	
	if ($selProjID != -1) {
		?>
		<div class="assetinput" >
		<span id="btnToggleDisplay" onclick="toggleAddResource('addresource', this);" style="cursor:pointer"> [Add a new asset]</span>
		<div id="addresource" class="assetinput" style="display:none;"><br/>	
		<table>
		<?php 
		if(!$is_iphone) {
		?>
			<tr><td colspan='3'><strong><label id='lblSynAssetMode'>Upload new asset mode</label></strong></td></tr>
			<tr><td><label for="file">File:</label></td>
			<td><input type="file" name='flUserfile' id="flUserfile"/><label id="lblAssetBrowse"><i></i>&nbsp;Maximum file size is 30MB</i></label></td><td></td></tr>
			<tr><td></td><td colspan='2'>Supported file types: doc, docx, txt, pdf, ppt, pptx, xls, xlsx, rtf, mp3, wav, wma, mpg, mpeg, 3gp, jpg, jpeg, png, gif</td></tr>
			
		<?php 
		}
		?><tr><td><label>Title:</label></td>
		<td><input type='text', id='txtTitle' name='txtTitle' maxlength='190'/></td><td></td></tr>
		<tr><td><label>Description:</label></td>
		<td colspan="2"><textarea rows="5" cols="30" name="txtAssetDescription" id="txtAssetDescription" onkeydown="textDescriptionLimit(this, 500);"></textarea></td></tr>
		<tr><td><label>Categories:</label></td>	
		<td colspan='2'>
		<select id='ldbSynAssetKeywords' name='ldbSynAssetKeywords[]' size='4' multiple="multiple">
		<?php 
		foreach (get_keywords() as $rowAssetKeyword) {
			echo "<option value=$rowAssetKeyword->KEY_ID>$rowAssetKeyword->KEYWORD</option>";
		}
		?>
		
		
		</select>&nbsp;Hold Ctrl key to select multiple keywords	
		</td>
		</tr>
		</table>
		<input type='hidden' name='hdResID' id='hdResID'/>
		<input type='hidden' name='updateMode' id='updateMode' value='uploadNew'/>			  
		<input type='checkbox' name='chkAgree' id='chkAgree' onclick="enableUpload();"/>&nbsp;<label>I accept the</label><a href='http://video.google.com/support/bin/answer.py?hl=en&answer=31704' target='_blank'>rules.</a><br/><br/>
		<button type='submit' name='btnUpload' id='btnUpload' disabled value='Upload' onclick="return validateUpload();">Upload</button>
		<button type='reset' name='btnSynAssetCancel'  id='btnSynAssetCancel'  onclick="clearReplaceMode();">Clear</button>
		</div>
		</div>
	<?php 	

	}
	global $wpdb;	
	echo $before_widget; 
	
	if (isset($_POST[btnUpload]) && ($_POST[btnUpload] == "Upload" && (isset($_POST[updateMode]) && ($_POST[updateMode] == "editInfo")))) {
		update_asset_info();
	}
	else {
		if(isset($_POST[btnUpload]) && ($_POST[btnUpload] == "Upload") && (isset($_FILES[flUserfile][name])) && ($_FILES[flUserfile][name] != "")) {
			$mode = "insert";
			if(isset($_POST[hdResID]) && ($_POST[hdResID] != "")) {
				$mode = "update";
			}
			upload_asset($mode, $selProjID);			
		}
		else if(isset($_POST[btnDelete]) && (strpos('$_POST[btnDelete]', 'Delete'))) {
			
			$resID = substr($_POST[btnDelete], 6);
			delete_asset($resID);
		}		
	}
	if ($selProjID != -1) {
		if(isset($_POST[btnSynSearchSubmit]) && $_POST[btnSynSearchSubmit] == "find") {
			populate_assets_by_search_criteria($searchDisplay);						
		}
		else {	
			get_assets_by_project($selProjID);		
		}
	}	
	echo "</form>";
	echo "</div>";
	echo $after_widget;     
}


/**
 * Get all keywords 
 * @return	resultset keyword details
 */
function get_keywords() {
	global $wpdb;
	$table = $wpdb->prefix."keywords";
	$myrows = $wpdb->get_results("SELECT * FROM $table ORDER BY KEYWORD ASC");	
	return $myrows;	
}


/**
 * Get assets of a particular project
 * @param	int  projectID of the project
 */
function get_assets_by_project($selProjID) {
	global $wpdb;
	$table = $wpdb->prefix."asset";
	$projTable = $wpdb->prefix."projects";
	$myrows = $wpdb->get_results("SELECT DISTINCT $table.RES_ID, $table.RES_NAME, $table.RES_TITLE, $table.RES_PATH, $table.RES_DATE, 
	$table.RES_TYPE, $table.DESCRIPTION, $table.PROJECT_ID, $table.CONTRIBUTOR_ID FROM $table, $projTable WHERE PROJECT_ID = $selProjID 
	AND $table.IS_DELETED='0' AND $projTable.IS_DELETED = '0' ORDER BY $table.RES_DATE DESC");	
	display_assets($myrows, 0);	
}


/**
 * Get project name by project ID
 * @param	int  projectID of the project
 * @return	string	project name
 */
function get_project_by_ID($selProjID) {
	global $wpdb;
	$table = $wpdb->prefix."projects";
	$myrows = $wpdb->get_results("SELECT PROJ_NAME FROM $table WHERE PROJ_ID = $selProjID AND IS_DELETED='0'");	
	foreach($myrows as $myrow) {
		return $myrow->PROJ_NAME;
	}
}


/**
 * Search assets by projects, contributors, keywords, type and upload dates
 * @param	int  projectID	int	contributorID	int	keywordID	string resource type	dateTime	uploaded date
 */
function populate_assets_by_search_criteria() {
	global $wpdb;
	global $current_user;
	$projTable = $wpdb->prefix."projects";
	$assetTable = $wpdb->prefix."asset";
	$assetKeywords = $wpdb->prefix."assetkeywords";
	$searchSql = "SELECT DISTINCT a.RES_ID, a.RES_NAME, a.RES_TITLE, a.RES_PATH, a.RES_DATE, a.RES_TYPE, a.CONTRIBUTOR_ID, 
	a.PROJECT_ID, a.DESCRIPTION FROM $assetTable as a, $assetKeywords as b WHERE a.IS_DELETED = '0'";
	$count = 0;
	if(!isset($_POST[ldbSynSearchProject])) {
		foreach(get_projects_by_contributor($current_user->ID) as $contri) {
			if ($count == 0) {
				$searchSql .= " AND (a.PROJECT_ID = $contri->PROJ_ID";
			}
			else {
				$searchSql .= " OR a.PROJECT_ID = $contri->PROJ_ID";
			}
			$count++;
		}
		$searchSql .= ")";		
	}	
	else {
		$searchProjItemCount = sizeof($_POST[ldbSynSearchProject]);
		for($projIndex=0; $projIndex<$searchProjItemCount; $projIndex++) {
			if(($_POST[ldbSynSearchProject][$projIndex] != "<select project>") || ($_POST[ldbSynSearchProject][$projIndex] != "")) {
				$tempValue = $_POST[ldbSynSearchProject][$projIndex];
				if($projIndex==0) {
					
					$searchSql .= " AND (a.PROJECT_ID = $tempValue";	
				}
				else {
					$searchSql .= " OR a.PROJECT_ID = $tempValue";				
				}
				if($projIndex == $searchProjItemCount-1) {
					$searchSql .= ")";
				}
					
			}
		}
	}
	
	$searchContItemCount = sizeof($_POST[ldbSynSearchContributor]);
	for($contrIndex=0; $contrIndex<$searchContItemCount; $contrIndex++) {
		if(($_POST[ldbSynSearchContributor][$contrIndex] != "<select project>") || ($_POST[ldbSynSearchContributor][$contrIndex] != "")) {
			$tempValue = $_POST[ldbSynSearchContributor][$contrIndex];
			if($contrIndex==0) {
				$searchSql .= " AND (a.CONTRIBUTOR_ID = $tempValue";	
			}
			else {
				$searchSql .= " OR a.CONTRIBUTOR_ID = $tempValue";
			}
			if($contrIndex == $searchContItemCount-1) {
				$searchSql .= ")";
			}
					
		}
	}
	
	
	$searchKeywordItemCount = sizeof($_POST[ldbSynSearchKeyword]);
	for($keywordIndex=0; $keywordIndex<$searchKeywordItemCount; $keywordIndex++) {
		if(($_POST[ldbSynSearchKeyword][$keywordIndex] != "<select project>") || ($_POST[ldbSynSearchKeyword][$keywordIndex] != "")) {
			$tempValue = $_POST[ldbSynSearchKeyword][$keywordIndex];
			if($keywordIndex==0) {
				$searchSql .= " AND ((b.KEY_ID = $tempValue AND b.RES_ID = a.RES_ID)";	
			}
			else {
				$searchSql .= " OR (b.KEY_ID = $tempValue AND b.RES_ID = a.RES_ID)";
			}
			if($keywordIndex == $searchKeywordItemCount-1) {
				$searchSql .= ")";
			}
					
		}
	}	
	
	$isBeginFlag = 0;
	$isEndFlag = false;
	if(($_POST[chkSynSearchDocument] != "")) {
		$searchSql .= " AND (a.RES_TYPE = 'document'";
		$isBeginFlag = 1;
	}
	if(($_POST[chkSynSearchImage] != "")) {
		if($isBeginFlag == 0) {
			$searchSql .= " AND (a.RES_TYPE = 'image'";
			$isBeginFlag = 2;
		}
		else {
			$searchSql .= " OR a.RES_TYPE = 'image'";
		}		
	}
	if(($_POST[chkSynSearchAudio] != "")) {
		if($isBeginFlag == 0) {
			$searchSql .= " AND (a.RES_TYPE = 'audio'";
			$isBeginFlag = 3;
		}
		else {
			$searchSql .= " OR a.RES_TYPE = 'audio'";
		}		
	}
	if(($_POST[chkSynSearchVideo] != "")) {
		if($isBeginFlag == 0) {
			$searchSql .= " AND (a.RES_TYPE = 'video'";
			$isBeginFlag = 4;
		}
		else {
			$searchSql .= " OR a.RES_TYPE = 'video'";
		}		
	}
	if($isBeginFlag != 0) {
		$searchSql .= ")";		
	}	
	if(trim($_POST[txtLiveSearch]) != "") {
		$searchValue = trim($_POST[txtLiveSearch]);
		$searchSql .= " AND (a.RES_TITLE LIKE '".cleanAssetInput($searchValue)."%')";
	}
	if(trim($_POST[dtpSysSearchFrom]) != "") {
		$tmpFrom .= trim($_POST[dtpSysSearchFrom])." "."00:00:00";
		$timeStampFrom = convert_asset_datetime($tmpFrom);
		$searchDateFrom = date("Y-m-d G:i:s", $timeStampFrom);		
		$searchSql .= " AND a.RES_DATE >= '$searchDateFrom'";
	}
	if(trim($_POST[dtpSysSearchTo]) != "") {
		$tmpTo .= trim($_POST[dtpSysSearchTo])." "."00:00:00";
		$timeStampTo = convert_asset_datetime($tmpTo);
		$searchDateTo = date("Y-m-d G:i:s", $timeStampTo);
		$searchSql .= " AND a.RES_DATE < '$searchDateTo'";
	}
	
	$searchSql .= " ORDER BY a.RES_DATE DESC";
	$myrows = $wpdb->get_results($searchSql);
	display_assets($myrows, 1);	
	
}



/**
 * Display assets of selected project(s) based on the criteria
 * @param	resultset result set of asset list
 */
function display_assets($myrows, $searhDisplay) {	
	$i = 0;
	$sFlag = $searhDisplay;
	$fulKeywords = "";
	$resourceType = "";
	global $current_user;
	echo "<table class='assetsTable' border='0'>";	
	foreach ($myrows as $myrow) {	
				
		if($myrow->CONTRIBUTOR_ID == $current_user->ID) {
			
			echo"<tr><td class='assetLeft'>";
	 		if($myrow->RES_TYPE == "image") {
	 			echo"<a href='$myrow->RES_PATH' target='_blank'><img src='assets/thumbnail/".$myrow->RES_NAME."' border='0'></a>"; 
	 			$resourceType = "Picture";
	 		}
			else if($myrow->RES_TYPE == "video") {
				$arrThumbComp = explode(".", $myrow->RES_NAME);
				$thumbFileName = "";
				for($ix=0; $ix<sizeof($arrThumbComp)-1; $ix++) {
					$thumbFileName .= $arrThumbComp[$ix];			
				}
				$thumbFileName .= ".png";
				echo"<a href='$myrow->RES_PATH' target='_blank'><img src='assets/thumbnail/".$thumbFileName."' border='0'></a>"; 	
	 			$resourceType = "Video";
			}
			else if($myrow->RES_TYPE == "document") {			
	 			echo"<a href='$myrow->RES_PATH'><img src='assets/thumbnail/document.png' border='0'></a>";  	 
	 			$resourceType = "Document";
			}
			else if($myrow->RES_TYPE == "audio") {
				echo"<a href='$myrow->RES_PATH' target='_blank'><img src='assets/thumbnail/audio.png' border='0'></a>";  	
	 			$resourceType = "Audio";
			} 		
			$contributorFullName = "";
			foreach(get_user_detail_byID($myrow->CONTRIBUTOR_ID) as $userRow) {
				$contributorFullName = $userRow->first_name." ".$userRow->last_name;
			}
			$assetTimestamp = convert_asset_datetime($myrow->RES_DATE);	 		
	 		echo "</td>";
			echo "<td align='left'>";
			echo "<div class='assetTitle'><a href='$myrow->RES_PATH' target='_blank'>* $myrow->RES_TITLE</a></div>\n";
			echo "<div class='assetAuthor'>$resourceType by <b>". $contributorFullName ."</b>, ".date('F j, Y g:ia',$assetTimestamp)."</div>\n";
			echo "<div class='assetDescription'>" . $myrow->DESCRIPTION . "</div>";
			echo "<div class='assetCategory'>";
			$tmpKeyWordsArr = "";
			$itemIndex = 0;
			foreach (get_keywords_by_resID($myrow->RES_ID) as $resKeyword) {			
				if($itemIndex == 0){
					echo $resKeyword->KEYWORD;		
					$tmpKeyWordsArr .= $resKeyword->KEY_ID;			
				}
				else {
			 		echo ", ".$resKeyword->KEYWORD;	
			 		$tmpKeyWordsArr .= "#".$resKeyword->KEY_ID;
				}
				$itemIndex++;		
			}			
			echo "</div>";
			echo "<input type='hidden' name='tmpKeywordID$myrow->RES_ID' id='tmpKeywordID$myrow->RES_ID' value='$tmpKeyWordsArr'/>";			
			echo "<input type='hidden' name='resID$i' value='$myrow->RES_ID'/>&nbsp;&nbsp;";
			echo "<input type='hidden' name='tmpResTitle$myrow->RES_ID' id='tmpResTitle$myrow->RES_ID' value='$myrow->RES_TITLE'/>";
			echo "<input type='hidden' name='tmpDescription$myrow->RES_ID' id='tmpDescription$myrow->RES_ID' value='$myrow->DESCRIPTION'/>";
			echo"</td>";
			echo "<td align='right' class='assetButtons'>";
			echo "<button type='submit' class='link' name='btnDelete' id='btnDelete' value='Delete$myrow->RES_ID' onclick='return confirmAssetDelete(0);'><span>Delete</span></button>";
			echo "<button type='button' name='btnReplace' id='btnReplace$myrow->RES_ID' value='Replace$myrow->RES_ID'  class='link' onclick='replaceAsset($myrow->RES_ID);'><span>Replace</span></button>";
			echo "<button type='button' name='btnEditInfo' id='btnEditInfo$myrow->RES_ID' value='EditInfo$myrow->RES_ID'  class='link' onclick='editAssetInfo($myrow->RES_ID);'><span>Edit&nbsp;Info</span></button>\n";
			echo "</div>";
		}
		else {
			echo"<tr><td class='assetLeft'>";
	 		if($myrow->RES_TYPE == "image") {
	 			echo"<a href='$myrow->RES_PATH' target='_blank'><img src='assets/thumbnail/".$myrow->RES_NAME."' border='0'></a>"; 
	 			$resourceType = "Picture";
	 		}
			else if($myrow->RES_TYPE == "video") {
				$arrThumbComp = explode(".", $myrow->RES_NAME);
				$thumbFileName = "";
				for($ix=0; $ix<sizeof($arrThumbComp)-1; $ix++) {
					$thumbFileName .= $arrThumbComp[$ix];			
				}
				$thumbFileName .= ".png";
				echo"<a href='$myrow->RES_PATH' target='_blank'><img src='assets/thumbnail/".$thumbFileName."' border='0'></a>"; 	
	 			$resourceType = "Video";
			}
			else if($myrow->RES_TYPE == "document") {			
	 			echo"<a href='$myrow->RES_PATH'><img src='assets/thumbnail/document.png' border='0'></a>";  	 
	 			$resourceType = "Document";
			}
			else if($myrow->RES_TYPE == "audio") {
				echo"<a href='$myrow->RES_PATH' target='_blank'><img src='assets/thumbnail/audio.png' border='0'></a>";  	
	 			$resourceType = "Audio";
			} 		
			$contributorFullName = "";
			foreach(get_user_detail_byID($myrow->CONTRIBUTOR_ID) as $userRow) {
				$contributorFullName = $userRow->first_name." ".$userRow->last_name;
			}
			$assetTimestamp = convert_asset_datetime($myrow->RES_DATE);
	 		echo "</td>";
			echo "<td align='left'>\n";
			echo "<div class='assetTitle'><a href='$myrow->RES_PATH' target='_blank'>$myrow->RES_TITLE</a></div>\n";
			echo "<div class='assetAuthor'>$resourceType by ". $contributorFullName .", ".date('F j, Y g:ia',$assetTimestamp)."</div>\n";
			echo "<div class='assetDescription'>" . $myrow->DESCRIPTION."</div>";
			echo "<div class='assetCategory'>";
			$tmpKeyWordsArr = "";
			$itemIndex = 0;
			foreach (get_keywords_by_resID($myrow->RES_ID) as $resKeyword) {			
				if($itemIndex == 0){
					echo $resKeyword->KEYWORD;		
					$tmpKeyWordsArr .= $resKeyword->KEY_ID;			
				}
				else {
			 		echo ",&nbsp;".$resKeyword->KEYWORD;	
			 		$tmpKeyWordsArr .= "#".$resKeyword->KEY_ID;
				}
				$itemIndex++;		
			}			
			echo "</div>";
			if(get_manager_of_project_byID($myrow->PROJECT_ID) == $current_user->ID) {			
				echo "<input type='hidden' name='tmpKeywordID$myrow->RES_ID' id='tmpKeywordID$myrow->RES_ID' value='$tmpKeyWordsArr'/>";			
				echo "<input type='hidden' name='resID$i' value='$myrow->RES_ID'/>&nbsp;&nbsp;";
				echo "<input type='hidden' name='tmpResTitle$myrow->RES_ID' id='tmpResTitle$myrow->RES_ID' value='$myrow->RES_TITLE'/>";
				echo "<input type='hidden' name='tmpDescription$myrow->RES_ID' id='tmpDescription$myrow->RES_ID' value='$myrow->DESCRIPTION'/>";
				echo "<input type='hidden' name='contributorName' id='contributorName$myrow->RES_ID' value='$contributorFullName'/>";	
				echo"</td>";
				echo "<td align='right' class='assetButtons'>";
				echo "<button type='submit' class='link' name='btnDelete' id='btnDelete' value='Delete$myrow->RES_ID' onclick='return confirmAssetDelete($myrow->RES_ID);'><span>Delete</span></button>";
				echo "<button type='button' name='btnReplace' id='btnReplace$myrow->RES_ID' value='Replace$myrow->RES_ID'  class='link' onclick='replaceAsset($myrow->RES_ID);'><span>Replace</span></button>";
				echo "<button type='button' name='btnEditInfo' id='btnEditInfo$myrow->RES_ID' value='EditInfo$myrow->RES_ID'  class='link' onclick='editAssetInfo($myrow->RES_ID);'><span>Edit&nbsp;Info</span></button>\n";
			}
			
		}		
		echo "</td></tr>";
		$i++;		
 	}
 	echo "</table>"; 
  	if($i == 0) {
  		if($sFlag == 0){
			echo "<div class='noasset'>No assets for selected project.</div>";			
		}
		else {
			echo "<div class='noasset'>No assets for selected search criteria.</div>";
		}
  	}
	echo "</div>";
}

/**
 * Get manager of a particular project
 * @param	int  project ID
 * @return	int	 manager ID
 */
function get_manager_of_project_byID($projID){
	global $wpdb;	
	$projTable = $wpdb->prefix."projects";
	$value = $wpdb->get_var("SELECT MANAGER_ID FROM $projTable WHERE PROJ_ID = $projID");	
	return $value;
}


/**
 * Convert date time to timestamp
 * @param	string  datetime
 * @return	string timestamp
 */
function convert_asset_datetime($str) {
	list($date, $time) = explode(' ', $str);
	list($year, $month, $day) = explode('-', $date);
	list($hour, $minute, $second) = explode(':', $time);
	
	$timestamp = mktime($hour, $minute, $second, $month, $day, $year);
	
	return $timestamp;
}


function update_asset_info() {
	global $wpdb;
	$assetTable = $wpdb->prefix."asset";
	$assetKeywordTable = $wpdb->prefix."assetkeywords";
	$updateSchema = "UPDATE $assetTable SET RES_TITLE='".cleanAssetInput($_POST[txtTitle])."', DESCRIPTION='".cleanAssetInput($_POST[txtAssetDescription])."' 
	WHERE RES_ID = $_POST[hdResID]";			
	$wpdb->query($updateSchema);
	$wpdb->query("DELETE FROM $assetKeywordTable WHERE RES_ID=$_POST[hdResID]");
	if(isset($_POST[ldbSynAssetKeywords]) && ($_POST[ldbSynAssetKeywords]!="")) {
		foreach($_POST[ldbSynAssetKeywords] as $KEY_ID) {
			$insertAssetKeyword = "INSERT INTO $assetKeywordTable(RES_ID, KEY_ID) VALUES($_POST[hdResID], $KEY_ID)";
			$wpdb->query($insertAssetKeyword);					
		}
	}			
}

/**
 * Get user details (firstname and lastname) of a particular user
 * @param	int  user ID
 * @return	resultset user detail
 */
function get_user_detail_byID($uID) {
	global $wpdb;	
	$users = $wpdb->prefix."users";
	$meta = $wpdb->prefix."usermeta";
	$myrows = $wpdb->get_results("SELECT u.id as id, a.meta_value as first_name, b.meta_value AS last_name " .
	         "FROM $users as u, $meta as a, $meta as b " .
			 "WHERE a.user_id = u.id AND b.user_id = u.id " .
			 "AND a.meta_key = 'first_name' AND b.meta_key = 'last_name' AND u.id = $uID");
	return $myrows;
}


/**
 * Get keyword's (categories) of a particular asset
 * @param	int  asset ID
 * @return	resultset keyword detail
 */
function get_keywords_by_resID($resID) {
	global $wpdb;	
	$keyTable = $wpdb->prefix."keywords";
	$assetKeyTable = $wpdb->prefix."assetkeywords";
	$myrows = $wpdb->get_results("SELECT a.KEYWORD, a.KEY_ID FROM $keyTable as a, $assetKeyTable as b WHERE a.KEY_ID = b.KEY_ID AND b.RES_ID = $resID");	
	return $myrows;
}


/**
 * Delete a selected asset from a project
 * @param	int  assetID of the asset
 */
function delete_asset($resID) {
	global $wpdb;
	$table = $wpdb->prefix."asset";
	$deleteSchema = "UPDATE $table SET IS_DELETED='1' WHERE RES_ID = $resID";
	$wpdb->query($deleteSchema);
}


/** 
 * Upload asset to the server and insert entry to the database
 * @param	string	mode of the upload eg; new or replace
 */
function upload_asset($mode, $selProjID) {
	if ($_FILES[flUserfile][size] > 31457280) {
		?>
		<script type="text/javascript">
		alert("File is too large to upload!");
		</script>
		<?php 		
	}
	
	$resTypeArr = explode("/",$_FILES[flUserfile][type]);
	$resType = $resTypeArr[0];
		if(strtoupper($resTypeArr[1]) == "3GPP") {
		$resType = "audio";
	}	
	if((strtoupper($resType) == "APPLICATION") || (strtoupper($resType) == "TEXT")) {
		$resType = "document";
	}	
	
	$sessionID = sprintf("%.12d",microtime(true));
	$resName = $sessionID."_".$_FILES[flUserfile][name];
	$resFullPath="assets/".$resType."/".$resName; // the path with the file name where the file will be stored, upload is the directory name.
		
	if(move_uploaded_file ($_FILES[flUserfile][tmp_name],$resFullPath)){
		$keywords = $_POST[txtLabel1].",".$_POST[txtLabel2].",".$_POST[txtLabel3];
		global $wpdb;
		global $current_user;	
		$RES_ID = 0;	
		$assetTable = $wpdb->prefix."asset";
		$assetKeywordTable = $wpdb->prefix."assetkeywords";
		if($mode == "insert") {
			$insertAssetSchema = "INSERT INTO $assetTable(RES_NAME, RES_TITLE, RES_PATH, RES_TYPE, IS_DELETED, CONTRIBUTOR_ID, PROJECT_ID, DESCRIPTION)
        	VALUES('".cleanAssetInput($resName)."', '".cleanAssetInput($_POST[txtTitle])."','".cleanAssetInput($resFullPath)."', '$resType', '0', $current_user->ID, $selProjID, '".cleanAssetInput($_POST[txtAssetDescription])."')";
			$wpdb->query($insertAssetSchema);
			$RES_ID = mysql_insert_id();
		
			
			if(isset($_POST[ldbSynAssetKeywords]) && ($_POST[ldbSynAssetKeywords]!="")) {
				for ($g=0; $g<sizeof($_POST[ldbSynAssetKeywords]); $g++) {
					$KEY_ID = $_POST[ldbSynAssetKeywords][$g];
					$insertAssetKeyword = "INSERT INTO $assetKeywordTable(RES_ID, KEY_ID) VALUES($RES_ID, $KEY_ID)";
					$wpdb->query($insertAssetKeyword);					
				}				
			}			
	    }
		else if($mode == "update") {			
			if(isset($_POST['updateMode']) || ($_POST['updateMode'] == "replaceAsset")) {
				$updateSchema = "UPDATE $assetTable SET RES_NAME='".cleanAssetInput($resName)."', RES_TITLE='".cleanAssetInput($_POST[txtTitle])."', RES_PATH='".cleanAssetInput($resFullPath)."', RES_TYPE='$resType', DESCRIPTION='".cleanAssetInput($_POST[txtAssetDescription])."' 
				WHERE RES_ID = $_POST[hdResID]";		
				$wpdb->query($updateSchema);
				$wpdb->query("DELETE FROM $assetKeywordTable WHERE RES_ID=$_POST[hdResID]");
				if(isset($_POST[ldbSynAssetKeywords]) && ($_POST[ldbSynAssetKeywords]!="")) {
					for ($g=0; $g<sizeof($_POST[ldbSynAssetKeywords]); $g++) {
						$KEY_ID = $_POST[ldbSynAssetKeywords][$g];
						$insertAssetKeyword = "INSERT INTO $assetKeywordTable(RES_ID, KEY_ID) VALUES($_POST[hdResID], $KEY_ID)";
						$wpdb->query($insertAssetKeyword);					
					}				
				}	
			}
		}
		generate_thumbnail($resFullPath, $resName);
		//chmod("$add",0777);	
	}
	else{
		?>
		<script type="text/javascript">
		alert("Failed to upload file Contact Site admin to fix the problem");
		</script>
		<?php 		
	}
}


/**
 * Sanitize Database Inputs
 * @param	string	input applying sanitize
 */
function cleanAssetInput($input) {

  $search = array(
    '@<script[^>]*?>.*?</script>@si',   // Strip out javascript
    '@<[\/\!]*?[^<>]*?>@si',            // Strip out HTML tags
    '@<style[^>]*?>.*?</style>@siU',    // Strip style tags properly
    '@<![\s\S]*?--[ \t\n\r]*>@'         // Strip multi-line comments
  );

    $output = preg_replace($search, '', $input);
    return $output;
}

/**
 * Generate thumbnail for images
 * @param	string resource relative path
 * @param	string resiurce name
 */
function generate_thumbnail($resFullPath, $resName) {
	///////// Start the thumbnail generation//////////////
	$n_width=75; // Fix the width of the thumb nail images
	$n_height=75; // Fix the height of the thumb nail imaage
	
	$tsrc="assets/thumbnail/".$resName; // Path where thumb nail image will be stored
	$ggg =  $_FILES[flUserfile][type];
	if (@strtoupper($_FILES[flUserfile][type])=="IMAGE/GIF") {
		$im=ImageCreateFromGIF($resFullPath);
		$width=ImageSx($im); // Original picture width is stored
		$height=ImageSy($im); // Original picture height is stored
		$newImage=imagecreatetruecolor($n_width,$n_height);
		imageCopyResized($newImage,$im,0,0,0,0,$n_width,$n_height,$width,$height);
		if (function_exists("imagegif")) {
			Header("Content-type: image/gif");
			ImageGIF($newImage,$tsrc);
		}
		elseif (function_exists("imagejpeg")) {
			Header("Content-type: image/jpeg");
			ImageJPE($newImage,$tsrc);
		}		
	}
		
	if(strtoupper($_FILES[flUserfile][type])=="IMAGE/JPEG"){		
		$im=ImageCreateFromJPEG($resFullPath);		
		$width=ImageSx($im); // Original picture width is stored
		$height=ImageSy($im); // Original picture height is stored
		$newImage=imagecreatetruecolor($n_width,$n_height);
		imageCopyResized($newImage,$im,0,0,0,0,$n_width,$n_height,$width,$height);
		ImageJpeg($newImage,$tsrc);		
	}
	
	if(strtoupper($_FILES[flUserfile][type])=="IMAGE/PNG"){		
		$im=imagecreatefrompng($resFullPath);		
		$width=ImageSx($im); // Original picture width is stored
		$height=ImageSy($im); // Original picture height is stored
		$newImage=imagecreatetruecolor($n_width,$n_height);
		imageCopyResized($newImage,$im,0,0,0,0,$n_width,$n_height,$width,$height);
		imagepng($newImage,$tsrc);
		//chmod("$tsrc",0777);
	}
	
	if(strlen(strpos(strtoupper($_FILES[flUserfile][type]), "VIDEO"))>0) {
		// movie.avi.... name of the movie you want to take a screenshot from
		// 00:00:00.... Where in the movie do you wanna take your screenshot, 10 seconds from start? ex: 00:00:10
		// picname..... name your generated pic
		//echo "video file done";
		$inputPath = getcwd()."\\assets\\video\\";
		$outputPath = getcwd()."\\assets\\thumbnail\\";
		$arrFileComp = explode(".", $resName);
		$fileName = "";
		for($index=0; $index<sizeof($arrFileComp)-1; $index++) {
			$fileName .= $arrFileComp[$index];			
		}
		$inputVideoName = $inputPath.$resName;
		$thumbnailName = $outputPath.$fileName.".png";
		convertToFlv($inputVideoName, $thumbnailName);				
	}	
}


/**
 * Create thumbnail for videos
 * @param	string input file name and the path
 * @param	string output file name and the path
 */
function convertToFlv($input, $output) {
	$pathToFfmpeg = get_option("ffmpegPath");
    $command = $pathToFfmpeg." -v 0 -y -i $input -vframes 1 -ss 2 -vcodec mjpeg -f rawvideo -s 75x75 -aspect 16:9 $output ";
    shell_exec( $command );   
}


/**
 * Install the syn_asset_mgmt plugin to the system. It will create 
 * all the tables in the wordpress database.
 */
function syn_asset_install() {
	global $wpdb;
	$resTable = $wpdb->prefix."asset";
	$keywordsTable = $wpdb->prefix."keywords";
	$dropSchema1 = "DROP TABLE IF EXISTS $resTable";
	$wpdb->query($dropSchema1);

	$resourceSchema = "CREATE TABLE IF NOT EXISTS $resTable (
        RES_ID INT(9) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        RES_NAME VARCHAR(200) NOT NULL,
        RES_TITLE VARCHAR(200) NOT NULL,
        RES_PATH VARCHAR(500) NOT NULL,
        RES_DATE TIMESTAMP DEFAULT NOW(),
        RES_TYPE VARCHAR(50) NOT NULL,
        IS_DELETED CHAR(1) NOT NULL,
        CONTRIBUTOR_ID INT(9) NOT NULL,
        PROJECT_ID INT(9) NOT NULL,
        DESCRIPTION VARCHAR(1000)               	
    );";
	
	
	$wpdb->query($resourceSchema);
	
	$resKeytable = $wpdb->prefix."assetKeywords";
	$dropSchema2 = "DROP TABLE IF EXISTS $resKeytable";
	$wpdb->query($dropSchema2);
	$resKeywordsSchema  = "CREATE TABLE $resKeytable (
        RES_ID INT(9) NOT NULL,
        KEY_ID INT(9) NOT NULL,						
    	FOREIGN KEY (RES_ID) REFERENCES $resTable (RES_ID) ON UPDATE CASCADE ON DELETE CASCADE,
    	FOREIGN KEY (KEY_ID) REFERENCES $keywordsTable (KEY_ID) ON UPDATE CASCADE ON DELETE CASCADE
    );";
	
	$wpdb->query($resKeywordsSchema);

	$configTable = $wpdb->prefix."synconfig";
	$configSchema = "CREATE TABLE IF NOT EXISTS $configTable (
        CONFIG_ID INT(9) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        CONFIG_NAME VARCHAR(100) NOT NULL,
        CONFIG_PATH VARCHAR(300) NOT NULL                     	
    );";
	$wpdb->query($configSchema);
}

// register plugin and install it to the wordpress	
add_action('activate_syn_asset_mgmt/syn_asset_main.php', 'syn_asset_install');

?>